package com.pet.dao;

import com.pet.bean.Account;
import com.pet.bean.Pet;
import com.pet.util.DataSourcesTool;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class AccountDao {
    //添加记录
    public void insert_Account(Connection con, Account account) throws SQLException {
        // 2、定义sql语句
        String sql = "insert into acount(sale_id,buy_id,type,order_date,price,pet_id) values(?,?,?,now(),?,?);";
        // 3、创建sql语句对象、如果sql语句有使用占位符，则给占位符赋值
        PreparedStatement ps = con.prepareStatement(sql);
        // 赋值操作
        ps.setInt(1, account.getSale_id());
        ps.setInt(2, account.getBuy_id());
        ps.setInt(3, account.getType());
        ps.setDouble(4, account.getPrice());
        ps.setInt(5, account.getPet_id());
        // 4、通过sql语句对象来执行sql操作
        ps.executeUpdate();

    }
    //通过sid找记录
    public List<Account> selectAccountByPetstore(Connection con, int s_id) throws SQLException {
        List<Account> list = new ArrayList<>();
        String sql = "select * from acount where sale_id=? and type=1 or buy_id=? and type=2";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setInt(1, s_id);
        ps.setInt(2, s_id);
        ResultSet resultSet = ps.executeQuery();
        while (resultSet.next()) {
            int sale_id = resultSet.getInt("sale_id");
            int buy_id = resultSet.getInt("buy_id");
            int type = resultSet.getInt("type");
            Date order_date = resultSet.getDate("order_date");
            double price = resultSet.getDouble("price");
            int p_id = resultSet.getInt("pet_id");
            Account account = new Account(sale_id, buy_id, type, order_date, price, p_id);
            list.add(account);
        }
        return list;
    }
    //通过oid找记录
    public List<Account> selectAccountByOwner(Connection con, int o_id) throws SQLException {
        List<Account> list = new ArrayList<>();
        String sql = "select * from acount where sale_id=? and type=2 or buy_id=? and type=1";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setInt(1, o_id);
        ps.setInt(2, o_id);
        ResultSet resultSet = ps.executeQuery();
        while (resultSet.next()) {
            int sale_id = resultSet.getInt("sale_id");
            int buy_id = resultSet.getInt("buy_id");
            int type = resultSet.getInt("type");
            Date order_date = resultSet.getDate("order_date");
            double price = resultSet.getDouble("price");
            int p_id = resultSet.getInt("pet_id");
            Account account = new Account(sale_id, buy_id, type, order_date, price, p_id);
            list.add(account);
        }
        return list;
    }
}
